package org.anddev.android.databasework;

import java.util.ArrayList;

import android.app.ListActivity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.os.Bundle;
import android.widget.ArrayAdapter;
import android.widget.EditText;

public class DataBaseWork extends ListActivity {	
	private final String MY_DATABASE_NAME = "myCoolDB_2";
	private final String MY_DATABASE_TABLE = "Users";

	/** Called when the activity is first created. */
	@Override
	public void onCreate(Bundle icicle) {
		super.onCreate(icicle);
		
		EditText et = new EditText(this);
		et.setSelection(et.getText().length());
		/* Will hold the 'Output' we want to display at the end. */
		ArrayList<String> results = new ArrayList<String>();

		SQLiteDatabase myDB = null;
		try {
						
			myDB = openOrCreateDatabase(MY_DATABASE_NAME, 0, null);

			/* Create a Table in the Database. */ 
			myDB.execSQL("CREATE TABLE IF NOT EXISTS "
							+ MY_DATABASE_TABLE
							+ " (LastName VARCHAR, FirstName VARCHAR,"
							+ " Country VARCHAR, Age INT(3));");

			/* Add two DataSets to the Table. */
			myDB.execSQL("INSERT INTO "
							+ MY_DATABASE_TABLE
							+ " (LastName, FirstName, Country, Age)"
							+ " VALUES ('Gramlich', 'Nicolas', 'Germany', 20);");
			myDB.execSQL("INSERT INTO "
							+ MY_DATABASE_TABLE
							+ " (LastName, FirstName, Country, Age)"
							+ " VALUES ('Doe', 'John', 'US', 34);");			

			/* Query for some results with Selection and Projection. */
			Cursor c = myDB.rawQuery("SELECT LastName, FirstName, Age" +
								" FROM " + MY_DATABASE_TABLE 
								+ " WHERE Age > 10 LIMIT 7;",
								null);
			/* Get the indices of the Columns we will need */
			int firstNameColumn = c.getColumnIndex("FirstName");
			int lastNameColumn = c.getColumnIndex("LastName");
			int ageColumn = c.getColumnIndex("Age");
			results.clear();
			/* Check if our result was valid. */
			if (c != null) {
				/* Check if at least one Result was returned. */ 
				if (c.isBeforeFirst()) {
					int i = 0;
					/* Loop through all Results */ 
					while (c.moveToNext())
					{
						i++;
						/* Retrieve the values of the Entry
						 * the Cursor is pointing to. */
						String lastName = c.getString(lastNameColumn);
						String firstName = c.getString(firstNameColumn);
						int age = c.getInt(ageColumn);
						/* We can also receive the Name 
						 * of a Column by its Index.
						 * Makes no sense, as we already 
						 * know the Name, but just to show we can ;) */
						String ageColumName = c.getColumnName(ageColumn);
						
						results.add("" + i + ": " + firstName + " " + lastName
						+ " (" + ageColumName + ": " + age + ")");
					} 
				}
			}

		} catch (SQLiteException e) {
		} finally {
			if (myDB != null)
				myDB.close();
		}

		this.setListAdapter(new ArrayAdapter<String>(this,
				android.R.layout.simple_list_item_1, results));
	}
}